<?php
require_once 'common.php';
require_once 'OaAppModel.php';

/**
 * 订单查询（扣费成功，失败日志）读老的（OA库）模型
 * @author xucongbin
 * @desc 具体调用时，再详细解释
 */
class OaGsOrderLogModel extends OpModel
{
    private $_conn;
    //private $_appModel;
    const TB_PAY_SUCC_TABLE = "TBSPPAYLOGHIS";
    const TB_PAY_FAIL_TABLE = "TBERRLOG";
    /**
     * 划账日志表
     * @var unknown_type
     */
    const TB_DEPOSIT_LOG_TABLE = "TBDEPOSITLOGHIS";
    /**
     * 发道具日志表
     * @var string 
     */
    const TB_SEND_TABLE = "TBSENDSPITEMLOGHIS";
    //消息类型,1表示预发送。2表示消息回馈
    const MSG_TYPE_SEND =1;
    const MSG_TYPE_CALLBACK =2;
    protected static $msgType = array(
        self::MSG_TYPE_SEND=>"（发送）",
        self::MSG_TYPE_CALLBACK=>"（回调）"
    ); 
    //(status=1 发送成功)|(status=2 发送失败)|(其它)
    const SEND_STATUS_SUCCESS = 1;
    const SEND_STATUS_FAILURE = 2;
    const SEND_STATUS_OTHER_NAME = "其它";
    protected static $sendStatus = array(
        self::SEND_STATUS_SUCCESS=>"发送成功",
        self::SEND_STATUS_FAILURE=>"发送失败"
        
    );
    public function __construct ($logger = null)
    {
        if (is_null($logger)) {
            $logger = getOaModelLogger();
        }
        parent::__construct($logger);
        $dbConfig = Oa_Util_Config::getSingleDbConfig(OAII_OA_DB_CLUSTER_ID);
        $_conn = oci_connect($dbConfig['user'], $dbConfig['pass'], 
        "{$dbConfig['host']}:{$dbConfig['port']}/{$dbConfig['name']}", 'AL32UTF8');
        if (! $_conn) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $this->_conn = $_conn;
        $this->_appModel = new OaAppModel(OaAppModel::APP_CONFIG_ENV_PRODUCTION, $logger);
    }
    public function getServiceInfo ($fromDate, $toDate)
    {
        $appIds = array(50524, 50999, 50555);
        $fromDate = date("Y-m-d 00:00:00", strtotime($fromDate));
        $toDate = date("Y-m-d 23:59:59", strtotime($toDate));
        $sql = "SELECT * FROM (
        	SELECT A.*, ROWNUM RN FROM (SELECT SUM(CC) AS DD, SERVICEID FROM (
        		SELECT COUNT(*) AS CC, SERVICEIDPLAYER AS SERVICEID FROM " . self::TB_PAY_SUCC_TABLE . " 
        		WHERE TRUNC(CALLTIME) BETWEEN TO_DATE(:fromDate, 'yyyy-mm-dd hh24:mi:ss') 
        AND TO_DATE(:toDate, 'yyyy-mm-dd hh24:mi:ss')
        		GROUP BY SERVICEIDPLAYER  UNION ALL  
        		SELECT COUNT(*) AS CC, SERVICEID FROM " . self::TB_PAY_FAIL_TABLE . "
        		WHERE TRUNC(CALLTIME) BETWEEN TO_DATE(:fromDate, 'yyyy-mm-dd hh24:mi:ss') 
        AND TO_DATE(:toDate, 'yyyy-mm-dd hh24:mi:ss')
        		GROUP BY SERVICEID ) GROUP BY SERVICEID ORDER BY DD DESC
        	) A WHERE ROWNUM <= :end
        ) WHERE RN >= :begin";
        $sql = "SELECT * FROM (
        	SELECT A.*, ROWNUM RN FROM (SELECT SUM(CC) AS DD, SERVICEID FROM (
        SELECT COUNT(*) AS CC, SERVICEID 
        FROM " . self::TB_DEPOSIT_LOG_TABLE ." 
        WHERE TRUNC(CALLTIME) BETWEEN TO_DATE(:fromDate, 'yyyy-mm-dd hh24:mi:ss') 
        AND TO_DATE(:toDate, 'yyyy-mm-dd hh24:mi:ss') 
        GROUP BY SERVICEID
        /*UNION ALL  
       	SELECT COUNT(*) AS CC, SERVICEIDSITE AS SERVICEID FROM " . self::TB_SEND_TABLE . " 
       	WHERE TRUNC(CALLTIME) BETWEEN TO_DATE(:fromDate, 'yyyy-mm-dd hh24:mi:ss') 
        AND TO_DATE(:toDate, 'yyyy-mm-dd hh24:mi:ss')
        GROUP BY SERVICEIDSITE*/ ) GROUP BY SERVICEID ORDER BY DD DESC
        	) A WHERE ROWNUM <= :end
        ) WHERE RN >= :begin";
        $st = oci_parse($this->_conn, $sql);
        $begin = 0;
        $end = 3;
        $idsStr = join("','", $appIds);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':begin', $begin);
        oci_bind_by_name($st, ':end', $end);
        if (! oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, - 1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC) === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        return ($rows);
    }
    /**
     * 获取扣费订单日志记录
     * @param boolean 扣费成功，，还是失败
     * @param date $fromDate 开始日期
     * @param date $toDate 结束日期
     * @param int $appId 查询应用ID   
     * @param int $offset 
     * @param int $count
     * @param int $uid 数字账号或盛大通行证
     * @param int $uidType 用户账号类型 1 盛大通行证， 2 数字账号 
     * @param string $orderId 查询订单号
     * 
     * @return array (ORDERID=>'订单号',LOGTIME=>'日志时间', SERVICEID=>'产品ID', USERID=>'数字账号或盛大通行证', APPNAME=>'应用名称')
     */
    public function getLogList ($orderTrue, $fromDate, $toDate, $appId, $offset, $count, $uid=null, $uidType=null, $orderId=null)
    {
        $fromDate = date("Y-m-d 00:00:00", strtotime($fromDate));
        $toDate = date("Y-m-d 23:59:59", strtotime($toDate));
        $table = self::TB_PAY_SUCC_TABLE;
        $timeField = "CALLTIME";
        $serviceField = "SERVICEIDPLAYER";
        
        if(!$orderTrue){
            $table = self::TB_PAY_FAIL_TABLE;
            //$timeField = "ERRTIME";
            $serviceField = "SERVICEID";
        }
        $sql = "SELECT * FROM 
        (SELECT ROWNUM RN, A.* FROM
	  	(SELECT CONTEXTID AS ORDERID, {$timeField} AS LOGTIME, {$serviceField} AS SERVICEID, USERID, USERIDTYPE  FROM ".$table."
		 WHERE 	{$serviceField} = :appId AND TRUNC({$timeField}) BETWEEN TO_DATE(:fromDate, 'yyyy-mm-dd hh24:mi:ss') 
        AND TO_DATE(:toDate, 'yyyy-mm-dd hh24:mi:ss') ";
        if($uid&&$uidType){
            $sql.= "AND USERID = TO_CHAR(:userId) AND USERIDTYPE = :uidType ";
            //$sql.= "AND USERIDTYPE = :uidType ";
        }
        if($orderId){
            $sql.= "AND CONTEXTID=TO_CHAR(:orderId) ";
        }
        $sql.="ORDER BY {$timeField} DESC 
        ) A WHERE ROWNUM<= :end
        ) WHERE RN> :begin ";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':appId', $appId);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        if($uid&&$uidType){
            oci_bind_by_name($st, ':userId', $uid);
            oci_bind_by_name($st, ':uidType', $uidType);
        }
        if($orderId){
            oci_bind_by_name($st, ':orderId', $orderId);
        }
        oci_bind_by_name($st, ':begin', $offset);
        $end = $offset+$count;
        oci_bind_by_name($st, ':end', $end);
        if (! oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, - 1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC) === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        
        $this->pasrseArr($rows);
        return $rows;
    }
	/**
     * 重构内容
     * @param array $arr
     * 
     * @return void
     */
    private function pasrseArr (&$arr)
    {
        if (! $arr) {
            return $arr;
        }
        $ids = array();
        foreach($arr as $row){
            $ids[$row['SERVICEID']] =1;
        }
        $apps = $this->_appModel->getAppMulti(array_keys($ids));
        foreach ($arr as $k => $row) {
            $arr[$k]['APPNAME'] = $apps[$row['SERVICEID']]['name'];
        }
    }
    /**
     * 获取审核状态记录条数
     * @param boolean 扣费成功，，还是失败
     * @param date $fromDate 开始日期
     * @param date $toDate 结束日期
     * @param int $appId 查询应用ID
     * @param int $uid 数字账号或盛大通行证
     * @param int $uidType 用户账号类型 1 盛大通行证， 2 数字账号
     * @param string $orderId 查询订单号
     * 
     * @return int
     */
    public function getLogCount ($orderTrue, $fromDate, $toDate, $appId, $uid=null, $uidType=null, $orderId=null)
    {
        $fromDate = date("Y-m-d 00:00:00", strtotime($fromDate));
        $toDate = date("Y-m-d 23:59:59", strtotime($toDate));
        $table = self::TB_PAY_SUCC_TABLE;
        $timeField = "CALLTIME";
        $serviceField = "SERVICEIDPLAYER";
        if(!$orderTrue){
            $table = self::TB_PAY_FAIL_TABLE;
            //$timeField = "ERRTIME";
            $serviceField = "SERVICEID";
        }
        $sql = "SELECT COUNT(*) FROM {$table} WHERE {$serviceField}=:appId AND {$timeField} BETWEEN TO_DATE(:fromDate, 'yyyy-mm-dd hh24:mi:ss') 
        AND TO_DATE(:toDate, 'yyyy-mm-dd hh24:mi:ss') ";
        if($uid&&$uidType){
            $sql.= "AND USERID = TO_CHAR(:userId) AND USERIDTYPE = :uidType ";
        }
        if($orderId){
            $sql.= "AND CONTEXTID=TO_CHAR(:orderId) ";
        }
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':appId', $appId);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        if($uid&&$uidType){
            oci_bind_by_name($st, ':userId', $uid);
            oci_bind_by_name($st, ':uidType', $uidType);
        }
        if($orderId){
            oci_bind_by_name($st, ':orderId', $orderId);
        }
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $row = oci_fetch_row($st);
        if ($row === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        return (int) $row[0];
    }

    /**
     * 获取订单日志详细信息
     * @param boolean $orderTrue
     * @param string $orderId
     * @param int $appId
     * 
     * @return array|boolean array(order=>订单信息
     * array(
     * 成功时: ORDERID=>'订单号',LOGTIME=>'日志时间',USERID=>'数字账号或盛大通行证',PAYTYPEID=>货币类型,AMOUNT=>货币额, ITEMINFO=>道具信息
     * 失败时:ORDERID=>'订单号',LOGTIME=>'日志时间',USERID=>'数字账号或盛大通行证',ERRCODE=>错误代码,ERRINFO=>错误信息
     * ),
     * log=>日志信息
     * array(
     * 
     * ))
     * 
     */
    public function getLogInfo($orderTrue,$orderId, $appId)
    {
        $appInfo = $this->_appModel->getApp($appId, array('pay_type', 'name'));
        //$appInfo = array('pay_type'=>5, 'name'=>'sd;lkjfldsk;lfk;lsk');
        if(!$appInfo||
        ($appInfo['pay_type']!=OaAppModel::APP_STATUS_AUDITING &&
        $appInfo['pay_type']!=OaAppModel::APP_STATUS_IN_OPERATION)
        ){
            return false;
        }
        
        $table = self::TB_PAY_SUCC_TABLE;
        $timeField = "CALLTIME";
        $serviceField = "SERVICEIDPLAYER";
        $field = "ORDERID, CALLTIME AS LOGTIME, SERVICEIDPLAYER AS SERVICEID, USERID, PAYTYPEID, AMOUNT, ITEMINFO";
        if(!$orderTrue){
            $table = self::TB_PAY_FAIL_TABLE;
            //$timeField = "ERRTIME";
            $serviceField = "SERVICEID";
            $field = "ORDERID, CALLTIME AS LOGTIME, SERVICEID, USERID, ERRCODE, ERRINFO";
        }
        $sql = "SELECT {$field}  FROM {$table}
		 WHERE 	{$serviceField} = :appId AND ORDERID=TO_CHAR(:orderId)";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':appId', $appId);
        oci_bind_by_name($st, ':orderId', $orderId);
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $orderInfo = oci_fetch_assoc($st);
        $this->_logger->info($orderInfo);
        if ($orderInfo === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        if($appInfo['pay_type']==OaAppModel::APP_STATUS_AUDITING){
            $logInfo = $this->getDepositLogHis($orderId, $appId);
        }else{
            $logInfo = $this->getSendItemLogHis($orderId, $appId);
        }
        return array('order'=>$orderInfo,'log'=>$logInfo);
    }
    /**
     * 获取划账日志
     * @param string $orderId 订单号
     * @param int $appId 产品ID
     * @return array|boolean
     */
    private function getDepositLogHis ($orderId, $appId)
    {
        $sql = "SELECT ORDERID,CALLTIME,USERID,PAYTYPEID,AMOUNT,RESULTCODE,RESULTMSG,MSGTYPE,STATUS 
        FROM " . self::TB_DEPOSIT_LOG_TABLE ." 
        WHERE SERVICEID = :appId
        AND ORDERID = :orderId 
        AND MSGTYPE IN (".join(",", array_keys(self::$msgType)).")";
        $st = oci_parse($this->_conn, $sql);
        //oci_bind_by_name($st, ':inApp', $appIdStr);
        oci_bind_by_name($st, ':appId', $appId);
        oci_bind_by_name($st, ':orderId', $orderId);
        if (! oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        //划账日志
        $row1 = array();
        //划账日志（回调）
        $row2 = array();
        while($row = oci_fetch_assoc($st))
        {
            $row['STATUS'] = isset(self::$sendStatus[$row['STATUS']])?self::$sendStatus[$row['STATUS']]:self::SEND_STATUS_OTHER_NAME;
            if($row['MSGTYPE']==self::MSG_TYPE_SEND){
                $row1 = $row;
            }else{
                $row2 = $row; 
            }   
            if($row1&&$row2){
                break;
            }
        }
        $rows = array('send'=>$row1, 'callback'=>$row2);
        if ($row1 === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        return $rows;
    }
    /**
     * 获取发道具日志
     * @param string $orderId
     * @param int $appId
     */
    private function getSendItemLogHis($orderId, $appId)
    {
        $sql = "SELECT ORDERID,CALLTIME,USERID,ITEMINFO,STATUS,RESULTCODE,RESULTMSG,MSGTYPE 
        FROM " . self::TB_SEND_TABLE ." 
        WHERE SERVICEIDSITE = :appId
        AND ORDERID = :orderId 
        AND MSGTYPE IN (".join(",", array_keys(self::$msgType)).")";
        $st = oci_parse($this->_conn, $sql);
        //oci_bind_by_name($st, ':inApp', $appIdStr);
        oci_bind_by_name($st, ':appId', $appId);
        oci_bind_by_name($st, ':orderId', $orderId);
        if (! oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        //划账日志
        $row1 = array();
        //划账日志（回调）
        $row2 = array();
        while($row = oci_fetch_assoc($st))
        {
            $row['STATUS'] = isset(self::$sendStatus[$row['STATUS']])?self::$sendStatus[$row['STATUS']]:self::SEND_STATUS_OTHER_NAME;
            if($row['MSGTYPE']==self::MSG_TYPE_SEND){
                $row1 = $row;
            }else{
                $row2 = $row; 
            }   
            if($row1&&$row2){
                break;
            }
        }
        $rows = array('send'=>$row1, 'callback'=>$row2);
        if ($row1 === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        return $rows;
    }
}